![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Optimizing ArchivingArchiving is a normal part of daily operations and should be tuned to have the least effect on the performance of the system. Archiving is done when the instance is running in ARCHIVELOG mode. I recommend that you always run in ARCHIVELOG mode. The benefits of running in this mode are as follows:
You can archive manually or you can set the system up to archive automatically. Automatic archiving is enabled by setting the initialization parameter LOG_ARCHIVE_START to TRUE. Typically, archiving is done automatically with the archive process starting as soon as a log switch has occurred. Archiving usually has very little effect on the performance of the systembut there are some cases in which performance can be affected. In very large databases, the archive can have an effect on system performance. Physical placement of the log files can also cause performance problems. Remember that once the log switch has occurred, the archive process begins to sequentially read from the previous log file and write that information sequentially to the archive log destination. If multiple log files are on the same disk drive, you may actually cause a large amount of random disk activity switching between the current log file and the log file being archived.
Adjusting the Effect of ArchivingBy adjusting the initialization parameters LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE, you can either slow down or speed up the performance of archiving. By speeding up archiving, the effect on the system is of a shorter duration but is more noticeable. Slowing down archiving lengthens the duration but reduces the effect. To speed up archiving, use multiple archive buffers by setting LOG_ARCHIVE_BUFFERS to 2 or more and slowly increase the value of LOG_ARCHIVE_BUFFER_SIZE until you get the performance you want. Multiple buffers guarantee that a device such as a tape backup unit always has data available (that is, the device is streamed). To slow down archiving, set the value of LOG_ARCHIVE_BUFFERS to 1. Set the value of LOG_ARCHIVE_BUFFER_SIZE to the maximum allowed by your OS. If archiving is still happening too quickly, reduce the size of LOG_ARCHIVE_BUFFER_SIZE until you get the performance you desire.
Archiving is a necessary part of day-to-day operations that usually does not affect the system. For the cases in which archiving does adversely affect performance, slight configuration changes can usually solve the problem. Optimizing SortsThe default sort area size is usually sufficient for most applications. If your application often does sorts that do not fit into memory, you may want to adjust the size of the sort area. One way to determine whether your sorts are occurring in memory or on disk is to look at the dynamic performance table, V$SYSSTAT. The statistics of interest are given here:
You can look at these parameters by using the following SQL statement: SQL> SELECT name, value 2 FROM v$sysstat 3 WHERE name IN ('sorts (memory)', 'sorts (disk)'); NAME VALUE ---------------------------------------------------------------- -------- sorts (memory) 22 sorts (disk) 0 If you have a lot of sorts that use temporary space on disk, you may see a performance improvement by increasing the size of the sort area in memory. Do this by increasing the Oracle initialization parameter, SORT_AREA_SIZE. By increasing the sort area size, you see a benefit from faster sorts and less disk I/O.
If you increase the value for SORT_AREA_SIZE, you may want to decrease the value of SORT_AREA_RETAINED_SIZE (the amount of session memory retained for sorts). When Oracle performs sorts, it uses the amount of memory defined by SORT_AREA_SIZE. When the sort is completed, Oracle deallocates the memory, leaving as much as it can of the sort output in memory. Oracle deallocates memory until it reaches the value defined by SORT_AREA_RETAINED_SIZE. If the sort cannot fit into SORT_AREA_SIZE, the sort is broken into smaller pieces, which are sorted individually. These individual sorted pieces are known as runs. Usually, sorts are not a problem. However, you should periodically check the statistics and take appropriate action if necessary. Be sure to periodically monitor system memory usage as well.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |